package com.taha.mail.dbms;

/**
 * @author Zahra Ezati
 *
 */

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import com.taha.mail.entity.UserBean;

public class UserDataSourceImp implements UserDataSource<UserBean> {

	private static Statement stmt = null;
	String sql = "";
	
	public  boolean insert(Connection conn ,UserBean user){
		
        sql = "INSERT INTO APP.USERS VALUES ('"+
				   user.getUsername()+"','"+user.getPassword()+
				   "','"+user.getFirstname()+"','"+user.getLastname()+"')"; 
        int numRows = executeUpdate(conn,sql);
        if (numRows == 1)
        {
        	System.out.println(numRows + "row(s) affected.");
            return true;
        }
        else return false;
        
	}
		
	public String[] getUserNames(Connection conn) // conn parameter removed
    {	
		sql = "SELECT username FROM APP.USERS ORDER BY username";
        return runQuery(conn,sql);
        
    }
	

    public UserBean[] getUserList(Connection conn)
    {
        String query = "SELECT username, password, firstName, lastName " +
        		"FROM APP.USERS ORDER BY username";
        
        List list = Collections.synchronizedList(new ArrayList(200));
        
        try
        {
        	stmt = conn.createStatement();
            ResultSet results = stmt.executeQuery(query);
 
            while(results.next())
            {
                String username = results.getString(1);
                String password = results.getString(2);
                String firstName = results.getString(3);
                String lastName = results.getString(4);
                
                list.add(new UserBean(username,password,firstName,lastName));
            }
            results.close();
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
      
        return (UserBean[])list.toArray(new UserBean[list.size()]);
    }
    
    public static int executeUpdate(Connection conn, String sql)
    {
        // the number of rows affected by the update or insert
        int numRows = 0;
       
        try
        {
            stmt = conn.createStatement();
            numRows = stmt.executeUpdate(sql);
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
        return numRows;
    }
    
    public static String[] runQuery(Connection conn, String sql)
    {
        List list = Collections.synchronizedList(new ArrayList(10));
        try
        {
            stmt = conn.createStatement();
            ResultSet results = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = results.getMetaData();
            int numberCols = rsmd.getColumnCount();
       
            while(results.next())
            {
                    StringBuffer sbuf = new StringBuffer(200);
                    for (int i = 1; i <= numberCols; i++)
                    {
                        sbuf.append(results.getString(i));
                        //sbuf.append(", ");
                    }
                list.add(sbuf.toString());
            }
            results.close();
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
        return (String[])list.toArray(new String[list.size()]);
    }   
 
}
